package net.yxsoft.service.infrastructure;

/**
 * Created by YXLF on 2016/5/22.
 */

import com.jfinal.aop.Before;
import com.jfinal.kit.LogKit;
import com.jfinal.kit.PathKit;
import com.jfinal.kit.PropKit;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import net.yxsoft.bean.PowerImportModel;
import net.yxsoft.enumclass.LockEnumCls;
import net.yxsoft.enumclass.infrastructure.DbFieldTypeEnumCls;
import net.yxsoft.kit.CustomDb;
import net.yxsoft.kit.Helper;
import net.yxsoft.kit.PoiExporter;
import net.yxsoft.kit.WebUploaderKitDL;
import net.yxsoft.model.formdesigner.FormField;
import net.yxsoft.model.infrastructure.TsDatamodelProperties;
import net.yxsoft.model.infrastructure.TsParty;
import net.yxsoft.model.infrastructure.TsUdmkey;
import net.yxsoft.resource.DbSourceRes;
import net.yxsoft.resource.Res;
import net.yxsoft.service.common.BaseService;
import rapid.core.Authuser;
import rapid.core.ContextStateEnum;
import rapid.core.TResult;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class DataModelService_oracle extends BaseService {

  public static final DataModelService_oracle me = new DataModelService_oracle();
  private static final Helper helper = Helper.me;
  /**
   * 模型管理树初始化
   *
   * @return
   */
  public List<TsParty> loadDataModelTree() {

    List<TsParty> ls = TsParty.dao.find("select * from (select  to_char(conflict_id)  id,CONCAT(CONCAT(name, '|'), cnname) topic,par_party_id parentid from ts_party order by name) tb union all select '100' id,'模型管理' topic,'root' parentid from dual ");

    TsParty party = new TsParty();
    for(int i = 0; i < ls.size(); i++) {

      party = ls.get(i);
      /**
       * 如果上级节点号为root,则设置为isroot
       */
      if("root".equals(party.get("parentid"))) {

        party.put("isroot", true);
      }
    }

    return ls;
  }

  /**
   * 获取一级模型
   */
  public List<TsParty> loadParentModel() {

    List<TsParty> ls = TsParty.dao.find("select conflict_id ids,cnname from ts_party where root_party_flg = '1' order by sortno");

    return ls;
  }

  public TsParty findfirst(String name, String cnname) {

    //    return TsParty.dao.findFirst("select * from ts_party where name = ? or cnname = ?", name, cnname);
    return TsParty.dao.findFirst("select * from ts_party where name = ? and cnname = ?", name, cnname);
  }

  /**
   * 保存模型
   *
   * @param party
   * @return
   */
  @Before(Tx.class)
  public synchronized TResult saveModel(TsParty party) {
    TResult result = new TResult();
    boolean rs = false;

    try {

      String tableName = party.getName().toUpperCase();

      party.setName(tableName);


      TsParty _modelte = TsParty.dao.findFirst("select * from ts_party where name=? or cnname=?   ", tableName,party.getCnname());

      if(_modelte!=null){
        result.setState(ContextStateEnum.FAIL);
        result.setMessage("模型名称或中文名称重复。");
        return result;
      }

      if("100".equals(party.getParPartyId()) || !StrKit.notNull(party.getParPartyId())) {
        party.setParPartyName("ROOT");
        party.setParPartyId("100");

        String _top5 = tableName.substring(0, 5);

        TsParty _model = TsParty.dao.findFirst("select * from ts_party where substr(name,1,5) = ? and par_party_id='0' and par_party_name='ROOT'   ", _top5);

        if(_model != null) {
          result.setState(ContextStateEnum.FAIL);
          result.setMessage("模型名称前五位不能与当前存在的模型重复。");
          return result;
        }

      } else {
        String partyName = Db.queryStr("select name from ts_party where conflict_id = ?", party.getParPartyId());
        party.setParPartyName(partyName);
      }
      party.save();

      party.setRowId(party.getConflictId());
      party.update();

      String create_SQL = "CREATE TABLE " + tableName + "";
      String create_SEQ = "CREATE SEQUENCE SEQ_" + tableName + "";

      create_SQL += Db.getSql(PropKit.get(Res.KEY_SQLID_MODEL_CREATE + party.getPartySize()));
      create_SEQ += Db.getSql(PropKit.get(Res.KEY_SQLID_MODEL_SEQUENCE));
      String[] insertField_SQL = Db.getSql(PropKit.get(Res.KEY_SQLID_MODEL_INSERT + party.getPartySize())).replace("$1", String.valueOf(party.getConflictId())).replace("$2", party.getCnname()).split("//");

      List insertList = new ArrayList(Arrays.asList(insertField_SQL));

      CustomDb.updateWithExceptionHandling(create_SQL);
      if(!DbSourceRes.DB_SOURCE_MYSQL.equals(PropKit.get("dbType"))) {
        CustomDb.updateWithExceptionHandling(create_SEQ);
      }
      Db.batch(insertList, 1000);

      result.setState(ContextStateEnum.SUCCESS);
      result.setMessage("模型生成成功。");
    } catch(Exception e) {
      e.printStackTrace();
      throw new RuntimeException("保存模型异常", e);
    }

    return result;
  }

  @Before(Tx.class)
  public TResult removeModel(String key) {
    TResult _result = new TResult();
    TsParty _model = TsParty.dao.findById(key);

    if(_model == null) {
      _result.setState(ContextStateEnum.FAIL);
      _result.setMessage("该模型不存在，或已被删除，请刷新页面。");
      return _result;
    }

    List<String> _strSql = new ArrayList<String>();

    setRemoveSql(key, _model.getName(), _strSql);

    getAllChlidren(key, _strSql);

    //Db.batch(_strSql,10000);
    for(int i = 0; i < _strSql.size(); i++) {

      try {
        CustomDb.updateWithExceptionHandling(_strSql.get(i));
      } catch(Exception e) {
        LogKit.debug("对象不存在或已删除");
      }
    }
    _result.setState(ContextStateEnum.SUCCESS);
    _result.setMessage("删除成功。");
    return _result;
  }

  public void getAllChlidren(String key, List<String> strSql) {
    List<TsParty> _models = TsParty.dao.find("SELECT * FROM TS_PARTY WHERE PAR_PARTY_ID=?", key);
    if(_models != null) {
      for(TsParty _p : _models) {
        setRemoveSql(_p.getConflictId().toString(), _p.getName(), strSql);
        getAllChlidren(_p.getConflictId().toString(), strSql);
      }
    }
  }

  public void setRemoveSql(String key, String tableName, List<String> strSql) {

    strSql.add(String.format("DROP TABLE %S ", tableName));
    if(!DbSourceRes.DB_SOURCE_MYSQL.equals(PropKit.get("dbType"))) {
      strSql.add(String.format("DROP SEQUENCE SEQ_%S ", tableName));
    }
    strSql.add(String.format("DELETE FROM TS_PARTY WHERE conflict_id='%S' ", key));
    strSql.add(String.format("DELETE FROM TS_DATAMODEL_PROPERTIES WHERE MODEL_ID='%S' ", key));
    strSql.add(String.format("DELETE FROM TS_UDMKEY WHERE ATTRIB_01='%S' ", tableName));
  }

  /**
   * 查询模型名称 fjj
   *
   * @return
   */
  public List<TsParty> getPartyNameList() {
    List<TsParty> _models = TsParty.dao.find("select name, name || '|'|| cnname namegroup from ts_party order by name");
    return _models;
  }

  /**
   * 获取模型字段信息
   *
   * @param model model
   * @return List&lt;Contact&gt;
   */
  public List<TsDatamodelProperties> exportModelName(String model) {
    StringBuilder sql = new StringBuilder();
    sql.append(String.format(
      " SELECT\n" +
        " A.MODEL_ID,\n " +
        " A.MODEL_NAME,\n " +
        " A.STATE,\n " +
        " A.ATTRIB_NAME,\n " +
        " A.ATTRIB_DESCN,\n " +
        " A.ATTRIB_TYPE,\n " +
        " A.ATTRIB_TYPE_DESC,\n " +
        " CASE A.STATE WHEN '1' THEN '锁定' ELSE '激活' END STATE_CN,\n " +
        "B.NAME \n " +
        " FROM TS_DATAMODEL_PROPERTIES A \n" +
        " INNER JOIN  TS_PARTY B ON A.MODEL_ID=B.conflict_id  \n"));

    sql.append(" WHERE B.NAME = '" + model + "' AND A.STATE='1'");
    List<TsDatamodelProperties> datamodelProperties = TsDatamodelProperties.dao.find(sql.toString());
    return datamodelProperties;
  }

  /**
   * 导出模型信息
   *
   * @param model model
   * @return List&lt;Contact&gt;
   */
  public List<TsDatamodelProperties> exportModelName1(String model, String _tvals) {
    StringBuilder sql = new StringBuilder();
    sql.append(String.format(
      " SELECT\n" +
        " A.MODEL_ID,\n " +
        " A.MODEL_NAME,\n " +
        " A.STATE,\n " +
        " A.ATTRIB_NAME,\n " +
        " A.ATTRIB_DESCN,\n " +
        " A.ATTRIB_TYPE,\n " +
        " A.ATTRIB_TYPE_DESC,\n " +
        " CASE A.STATE WHEN '1' THEN '锁定' ELSE '激活' END STATE_CN,\n " +
        " A.DEFAULT_VAL,\n" +
        " A.FORMULA,\n" +
        "B.NAME,B.PARTY_SIZE \n " +
        " FROM TS_DATAMODEL_PROPERTIES A \n" +
        " INNER JOIN  TS_PARTY B ON A.MODEL_ID=B.conflict_id  \n"));

    sql.append(" WHERE B.NAME = '" + model + "' AND A.ATTRIB_NAME IN (" + _tvals + ") AND A.STATE='1'  ORDER BY A.ID,A.ATTRIB_NAME ASC");

    System.out.print(sql);

    List<TsDatamodelProperties> datamodelProperties = TsDatamodelProperties.dao.find(sql.toString());
    return datamodelProperties;
  }


  @Before(Tx.class)
  public TResult addDataModel(TsDatamodelProperties model) {

    TResult result = new TResult();

    String attribName = this.getLastAttribNameById(model.getModelId());
    if(StrKit.isBlank(attribName)) {    //抛出异常进行回滚
      result.setMessage("获取字段名失败！");
      result.setState(ContextStateEnum.FAIL);
      return result;
    } else {
      model.setAttribName(attribName);
    }

    TsDatamodelProperties _dbmodel = TsDatamodelProperties.dao.findFirst("SELECT * FROM TS_DATAMODEL_PROPERTIES WHERE MODEL_ID=? AND (ATTRIB_DESCN=? OR ATTRIB_NAME=?) ",
                                                                         model.getModelId(), model.getAttribDescn(), model.getAttribName());

    if(_dbmodel != null) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("字段名或字段描述已存在相同数据，请确认。");
      return result;
    }

    model.setState(Integer.parseInt(LockEnumCls.LOCKED.getValue()));

    if(StrKit.notBlank(model.getCodeName()) && model.getCodeName().indexOf("|") > 0) {
      String[] codes = model.getCodeName().split("\\|"); //切割 codename|customid|codetype
      if(codes.length == 3 && StrKit.notBlank(codes[0]) && StrKit.notBlank(codes[1]) && StrKit.notBlank(codes[2])) {
        model.setCodeName(codes[0]);
        model.setCodeCustomid(codes[1]);
        model.setCodeType(codes[2]);
        model.setCodeLeaf(StrKit.notBlank(model.getCodeLeaf()) ? model.getCodeLeaf() : "2");
      }
    } else {
      model.setCodeName(null);
      model.setCodeCustomid(null);
      model.setCodeType(null);
      model.setCodeLeaf(null);
    }
    model.setHandFlg("1"); //手动添加字段属性

    boolean _r = model.save();

    if(_r) {


      TsParty _model_party = TsParty.dao.findById(model.getModelId());
      String _tableName = _model_party.getName();

      String _str = String.format("ALTER TABLE %s ADD  (%s %s)", _tableName, model.getAttribName(), model.getAttribType());

      CustomDb.updateWithExceptionHandling(_str);

      result.setState(ContextStateEnum.SUCCESS);
      result.setMessage("保存成功。");

    } else {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("保存失败。");
    }

    return result;
  }

  @Before(Tx.class)
  public TResult updateDataModel(TsDatamodelProperties model) {

    TResult result = new TResult();

    if(model == null) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("当前信息不存在，或已被删除。");
      return result;
    }

    TsDatamodelProperties _dbmodel = TsDatamodelProperties.dao.findById(model.getId());

    if(_dbmodel == null) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("当前信息不存在，或已被删除。");
      return result;
    }

    TsDatamodelProperties _oldModel = TsDatamodelProperties.dao.findById(model.getId()); //原来的数据
    TsDatamodelProperties _dbmodel1 = TsDatamodelProperties.dao.findFirst("SELECT * FROM TS_DATAMODEL_PROPERTIES WHERE MODEL_ID=? AND ATTRIB_DESCN=? AND ID <> ?", _dbmodel.getModelId(), model.getAttribDescn(), _dbmodel.getId());

    if(_dbmodel1 != null) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("字段描述已存在相同数据，请确认。");
      return result;
    }


    result = this.isUpdateAttrib(_dbmodel, model); //判断是否可更改字段类型
    if(result.getState().getValue() == 1) {
      _dbmodel.setAttribType(model.getAttribType());
      _dbmodel.setAttribTypeDesc(model.getAttribTypeDesc());
    } else {
      return result;
    }
    _dbmodel.setAttribDescn(model.getAttribDescn()); //字段描述
    _dbmodel.setEncryFlg(model.getEncryFlg());
    _dbmodel.setTag(model.getTag());
    _dbmodel.setRequiredFlg(model.getRequiredFlg());
    if(StrKit.notBlank(model.getCodeName()) && model.getCodeName().indexOf("|") > 0) {
      String[] codes = model.getCodeName().split("\\|"); //切割 codename|customid|codetype
      if(codes.length == 3 && StrKit.notBlank(codes[0]) && StrKit.notBlank(codes[1]) && StrKit.notBlank(codes[2])) {
        _dbmodel.setCodeName(codes[0]);
        _dbmodel.setCodeCustomid(codes[1]);
        _dbmodel.setCodeType(codes[2]);
        _dbmodel.setCodeLeaf(StrKit.notBlank(model.getCodeLeaf()) ? model.getCodeLeaf() : "2");
      }
    } else {
      _dbmodel.setCodeName(null);
      _dbmodel.setCodeCustomid(null);
      _dbmodel.setCodeType(null);
      _dbmodel.setCodeLeaf(null);
    }
    _dbmodel.setTypeName(model.getTypeName());
    _dbmodel.setDefaultVal(model.getDefaultVal());
    _dbmodel.setFormula(model.getFormula());
    _dbmodel.setRemark(model.getRemark());
    _dbmodel.setState(Integer.parseInt(LockEnumCls.LOCKED.getValue()));
    boolean _r = _dbmodel.update();
    if(_r) {
      result.setState(ContextStateEnum.SUCCESS);
      result.setMessage("保存成功。");

      boolean _flg = false;

      String _type = _dbmodel.getAttribType();

      for(DbFieldTypeEnumCls d : DbFieldTypeEnumCls.COLLECTS_VARCHAR2) {
        if(d.equals(_type)) {
          _flg = true;
          break;
        }
      }
      if(_flg) {
        TsParty _model_party = TsParty.dao.findById(model.getModelId());
        String _tableName = _model_party.getName();
        String _str = String.format("ALTER TABLE %s MODIFY (%s %s)", _tableName, model.getAttribName(), model.getAttribType());
        CustomDb.updateWithExceptionHandling(_str);
      }



    } else {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("保存失败。");
    }

    return result;

  }

  @Before(Tx.class)
  public TResult removeDataModel(String dataModelId) {
    TResult result = new TResult();
    TsDatamodelProperties _dbmodel = TsDatamodelProperties.dao.findById(dataModelId);

    if(_dbmodel == null) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("当前信息不存在，或已被删除。");
      return result;
    }

    if(StrKit.isBlank(_dbmodel.getHandFlg()) || !"1".equals(_dbmodel.getHandFlg())) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("只能删除自定义新增的列。");
      return result;
    }

    try {
      TsDatamodelProperties.dao.deleteById(dataModelId);
      TsParty _model_party = TsParty.dao.findById(_dbmodel.getModelId());
      String _tableName = _model_party.getName();
      String _str = String.format("ALTER TABLE %s DROP (%s)", _tableName, _dbmodel.getAttribName());
      CustomDb.updateWithExceptionHandling(_str);
      result.setState(ContextStateEnum.SUCCESS);
      result.setMessage("删除成功。");
    } catch(Exception e) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("删除失败。");
    }

    return result;
  }

  @Before(Tx.class)
  public TResult resetDataModel(String dataModelId) {
    TResult result = new TResult();
    TsDatamodelProperties _dbmodel = TsDatamodelProperties.dao.findById(dataModelId);

    if(_dbmodel == null) {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("当前信息不存在，或已被删除。");
      return result;
    }

    _dbmodel.setAttribDescn(null);
    _dbmodel.setRequiredFlg(null);
    _dbmodel.setDefaultVal(null);
    _dbmodel.setTag(null);
    _dbmodel.setState(Integer.parseInt(LockEnumCls.UNLOCKED.getValue()));
    _dbmodel.setRemark(null);
    _dbmodel.setCodeName(null);
    _dbmodel.setFormula(null);
    _dbmodel.setTypeName(null);
    _dbmodel.setEncryFlg("0");
    _dbmodel.setCodeCustomid(null);
    _dbmodel.setCodeLeaf(null);
    _dbmodel.setCodeType(null);


    boolean _r = _dbmodel.update();

    if(_r) {
      result.setState(ContextStateEnum.SUCCESS);
      result.setMessage("重置成功。");
    } else {
      result.setState(ContextStateEnum.FAIL);
      result.setMessage("重置失败。");
    }

    return result;
  }

  //判断字段属性是否可更改
  private TResult isUpdateAttrib(TsDatamodelProperties oldModel, TsDatamodelProperties newModel) {
    TResult rt = new TResult();
    rt.setState(ContextStateEnum.FAIL);
    String _oldType = oldModel.getAttribType();  //新字段属性
    String _newType = newModel.getAttribType();  //新字段属性
    if(StrKit.isBlank(_oldType) || StrKit.isBlank(_newType)) {
      rt.setMessage("字段类型不能为空");
      return rt;
    }
    if(!"1".equals(oldModel.getHandFlg())) {  //非自增
      if(!_oldType.equals(_newType)) {
        rt.setMessage("非自定义字段无法更改类型");
        return rt;
      } else {
        rt.setState(ContextStateEnum.SUCCESS);
        rt.setMessage("可修改字段类型");
        return rt;
      }
    } else {
      if(_oldType.equals(_newType)) {
        rt.setState(ContextStateEnum.SUCCESS);
        rt.setMessage("可修改字段类型");
        return rt;
      }
    }
    boolean _flg = false;
    for(DbFieldTypeEnumCls d : DbFieldTypeEnumCls.COLLECTS_VARCHAR2) {
      if(d.equals(_newType)) {
        _flg = true;
        break;
      }
    }
    if(_flg) {
      String regEx = "[^0-9]";
      Pattern p = Pattern.compile(regEx);
      _oldType = p.matcher(_oldType).replaceAll("").trim();
      _newType = p.matcher(_newType).replaceAll("").trim();
      if(StrKit.notBlank(_oldType) && StrKit.notBlank(_newType)) {
        if(Integer.parseInt(_oldType) > Integer.parseInt(_newType)) {
          rt.setMessage("只能向上更改字符型(varchar)字段类型！");
          return rt;
        } else {
          rt.setState(ContextStateEnum.SUCCESS);
          rt.setMessage("可修改字段类型");
          return rt;
        }
      }
    } else {
      rt.setMessage("只能更改字符型(varchar)字段类型！");
      return rt;
    }
    rt.setMessage("字段类型错误，无法修改！");
    return rt;
  }


  //获取该model的最后一条数据 进行获取字段名
  private synchronized String getLastAttribNameById(Integer modelId) {
    TsDatamodelProperties model = TsDatamodelProperties.dao.findFirst("select * from ts_datamodel_properties where model_id= ? and nvl(attrib_name,' ')!=' '  order by id desc ", modelId);
    if(null != model) {
      String attribName = model.getAttribName();
      if(StrKit.notBlank(attribName) && attribName.indexOf("_") > 0) {
        String[] attr = attribName.split("_");
        if(attr.length == 2) {
          String _num = attr[1];
          if(this.isNumeric(_num)) {
            return attr[0] + "_" + (Integer.parseInt(_num) + 1);
          }
        }
      }
    }
    return null;
  }

  private boolean isNumeric(String str) {
    Pattern pattern = Pattern.compile("[0-9]*");
    Matcher isNum = pattern.matcher(str);
    if(!isNum.matches()) {
      return false;
    }
    return true;
  }

  //region 万能导入

  //获取模型表
  public List<Record> getTsPartyByPicker(String modelId) {
    String sql = "select conflict_id value,concat(concat(name,'|'),cnname) text from ts_party where 1=1";
    if(StrKit.notBlank(modelId)) {
      sql += String.format(" and conflict_id= %s ", modelId);
    }
    return Db.find(sql);
  }

  //根据模型获取导入主键
  public List<Record> getUdmKeyPickerByModelId(String modelId) {
    return Db.find("select conflict_id value,concat(concat(attrib_01,'--'),attrib_02) text from ts_udmkey where row_id=?", modelId);
  }

  //数据字典 万能导入规则
  public List<Record> getUpdateRule() {
    return CodeService.INSTANCE.getCodeByParam("MODEL", "RULE", "2");
  }

  public TResult powerImportExl(PowerImportModel model, Authuser user) {
    TResult rt = new TResult();
    rt.setState(ContextStateEnum.FAIL);
    if(null == model) {
      rt.setMessage("请选择数据！");
      return rt;
    }

    if(StrKit.isBlank(model.getModelId())) {
      rt.setMessage("请选择模型表！");
      return rt;
    }
    if(StrKit.isBlank(model.getUmdKey())) {
      rt.setMessage("请选择主键！");
      return rt;
    }
    if(StrKit.isBlank(model.getUpdateRule())) {
      rt.setMessage("请选择导入规则！");
      return rt;
    }
    if(StrKit.isBlank(model.getImportFile())) {
      rt.setMessage("请上传要导入的数据！");
      return rt;
    }
    TsParty tsParty = TsParty.dao.findById(model.getModelId());
    if(null == tsParty) {
      rt.setMessage("该模型表不存在！");
      return rt;
    }
    TsUdmkey udmKey = this.getUdmKeyByModelId(tsParty.getConflictId() + "", model.getUmdKey());
    if(null == udmKey) {
      rt.setMessage("导入模型主键不存在！");
      return rt;
    }
    List<Map<Integer, String>> list = PoiExporter.readExcel(PathKit.getWebRootPath() + model.getImportFile());
    if(null == list || list.size() == 0) {
      rt.setMessage("导入数据为空！");
      return rt;
    }

    if(StrKit.notBlank(model.getFnformid())){
      List<FormField> _fields = FormField.dao.find("SELECT * FROM FN_FORM_FIELD WHERE FORM_ID=?",model.getFnformid());
      model.setFormFields(_fields);
    }

    rt = WebUploaderKitDL.powerfulImport(model.getImportFile(),list, tsParty.getName(), udmKey.getAttrib02(), model.getUpdateRule(),model.getFormFields(),user,null);
    return rt;

  }

  //获取导入主键
  public TsUdmkey getUdmKeyByModelId(String modelId, String conflictId) {
    return TsUdmkey.dao.findFirst("select * from ts_udmkey where row_id=? and conflict_id=?", modelId, conflictId);
  }


  //endregion



}
